package com.java.myJdbcutils;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.java.annotation.ColumnMapper;

public class JdbcUtils {
	static {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static String url = "jdbc:oracle:thin:@localhost:1521:xe";
	public static String user = "LIKEJIN";
	public static String password = "123456";

	/**
	 * 获取连接
	 * 
	 * @return Connection
	 */
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 关闭连接
	 * 
	 * @param connection
	 */
	public static void closeStatement(Statement stat) {
		try {
			if (stat != null) {
				Connection connection = stat.getConnection();
				if (connection != null)
					connection.close();
				stat.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void closeConnection(Connection connection) {
		try {
			if (connection != null)
				connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void closeResultSet(ResultSet rs) {
		try {
			if (rs != null) {
				Statement stat = rs.getStatement();
				closeStatement(stat);
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int execute(String sql, Object[] params) {
		Connection connection = getConnection();
		if (connection == null) {
			return 0;
		}
		PreparedStatement stat = null;
		try {
			stat = connection.prepareStatement(sql);
			if (params != null) {
				int i = 1;
				for (Object object : params) {
					stat.setObject(i++, object);
				}
			}
			return stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeStatement(stat);
			closeConnection(connection);
		}
		return 0;
	}

	/**
	 * 查询
	 * 
	 * @param sql
	 * @param params
	 * @param clz
	 * @return
	 */
	public static <T> List<T> query(String sql, Object[] params, Class<T> clz) {
		Connection connection = JdbcUtils.getConnection();
		PreparedStatement stat;
		ResultSet rs = null;
		List<T> list = null;
		try {
			stat = connection.prepareStatement(sql);
			int i = 1;
			if (params != null) {
				for (Object object : params) {
					stat.setObject(i++, object);
				}
			}
			rs = stat.executeQuery();
			list = new ArrayList<T>();
			while (rs.next()) {
				list.add(setBeans(rs, clz));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closeConnection(connection);
		}
		return null;
	}

	/**
	 * 设置对象属性
	 * 
	 * @param rs
	 * @param clz
	 * @return
	 */
	private static <T> T setBeans(ResultSet rs, Class<T> clz) {
		try {
			Constructor<T> constructor = clz.getDeclaredConstructor();
			T ob = constructor.newInstance();

			Field[] fields = clz.getDeclaredFields();
			for (Field field : fields) {

				// ColumnMapper作为映射字段名的Annotation类
				ColumnMapper antion = field.getAnnotation(ColumnMapper.class);
				String colname = field.getName().toUpperCase();
				if ("SERIALVERSIONUID".equals(colname)) {
					continue;
				}
				if (antion != null) {
					colname = antion.value().toUpperCase();
				}
				Object dbValue = rs.getObject(colname);
				field.setAccessible(true);
				field.set(ob, exchange(field, dbValue));
			}
			return ob;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 转换数据类型
	 * 
	 * @param field
	 * @param dbValue
	 * @return
	 */
	private static Object exchange(Field field, Object dbValue) {
		String typeName = field.getType().getSimpleName();
		if (dbValue instanceof String) {
			return dbValue;
		} else if (dbValue instanceof Timestamp) {
			return dbValue;
		}
		if (typeName.equals("int") || typeName.equals("Integer")) {
			return Integer.parseInt(dbValue.toString());
		} else if (typeName.equals("long") || typeName.equals("Long")) {
			return Long.parseLong(dbValue.toString());
		} else if (typeName.equals("double") || typeName.equals("Double")) {
			return Double.parseDouble(dbValue.toString());
		} else if (typeName.equals("float") || typeName.equals("Float")) {
			return Float.parseFloat(dbValue.toString());
		}
		return null;
	}
}
